package pers.lance.jmsproject.dao;

import pers.lance.jmsproject.pojo.User;
import pers.lance.jmsproject.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Lance Li
 * @date 7/10/2018 3:11 PM
 * @desc
 */
public class UserDaoImp implements UserDao {

    public List<User> finAllUsers() {
        String sql = " select  * from User";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<User> Users = new ArrayList<User>();
            while (rs.next()) {
                User user = new User(rs.getInt("id"), rs.getString("uname"),
                        rs.getString("sex"), rs.getInt("age"), rs.getDate("date"));
                Users.add(user);
            }
            return Users;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public int addUser(User user) {
        String sql = "insert into User (id, uname, sex, age, birth) values(?,?,?,?,?)";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user.getId());
            pstmt.setString(1, user.getName());
            pstmt.setInt(1, user.getAge());
            pstmt.setString(1, user.getSex());
            pstmt.setDate(1, (Date) user.getBirth());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int updateUser(int id, User user) {
        String sql = " update User set uname = ?, sex = ?, age = ?, birth = ? where id = ?";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user.getId());
            pstmt.setString(1, user.getName());
            pstmt.setInt(1, user.getAge());
            pstmt.setString(1, user.getSex());
            pstmt.setDate(1, (Date) user.getBirth());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int deleteUser(int id) {
        String sql = " delete User where id = ?";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public User loadUser(int id) {
        String sql = " select  * from User where id = ?";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<User> Users = new ArrayList<User>();
            while (rs.next()) {
                return new User(rs.getInt("id"), rs.getString("uname"),
                        rs.getString("sex"), rs.getInt("age"), rs.getDate("date"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public int[] addUsers(List<User> Users) {
        String sql = "insert into User (id, uname, sex, age, birth) values(?,?,?,?,?)";
        Connection conn = DBUtil.getConnectionWithDpc();
        PreparedStatement pstmt = null;
        try {
            for (User user : Users) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, user.getId());
                pstmt.setString(1, user.getName());
                pstmt.setInt(1, user.getAge());
                pstmt.setString(1, user.getSex());
                pstmt.setDate(1, (Date) user.getBirth());
                pstmt.addBatch();
            }
            return pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }
}
